package model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;
import model.db.MyDBAccess;

public class SearchDB {
	HttpServletRequest request;
	String req_employee_no = "";
	String req_sex = "";
	String req_join_date_from = "";
	String req_join_date_to = "";
	String req_section = "";
	String req_action_id = "";
	String sql = "";

	// 検索用コンストラクタ
	public SearchDB(HttpServletRequest argrequest) {
		req_employee_no = argrequest.getParameter("employee_no");
		req_sex = argrequest.getParameter("sex");
		req_join_date_from = argrequest.getParameter("join_date_from");
		req_join_date_to = argrequest.getParameter("join_date_to");
		req_section = argrequest.getParameter("section");
		req_action_id = argrequest.getParameter("action_id");
	}

	// DBから検索結果レコードを取得するメソッド
	public ArrayList<Employee> searchresult() throws DBException {
		// 取得レコード格納リスト
		ArrayList<Employee> employees = new ArrayList<Employee>();
		// SQL文取得
		sql = createsql(req_employee_no, req_sex, req_join_date_from,
				req_join_date_to, req_section, req_action_id);
		// DBからレコードを取得
		MyDBAccess db = new MyDBAccess();
		try {
			db.open();
		} catch (Exception e) {
			throw new DBException("データベースオープンエラー", DBException.DB_OPEN_ERR);
		}
		ResultSet rs = null;
		try {
			rs = db.getResultSet(sql);
		} catch (Exception e) {
			throw new DBException("データベースからデータを取り出せないエラー",
					DBException.DB_RESULT_NOTHING);
		}
		// 取得された各結果に対しての処理
		try {
			while (rs.next()) {
				String sex = rs.getString("sex"); // 性別を取得
				String res_sex = "";
				if (sex.equals("1")) {
					res_sex = "男性";
				}
				if (sex.equals("2")) {
					res_sex = "女性";
				}
				Employee employee = new Employee();
				employee.employee_no = rs.getString("employee_no");
				employee.first_name = rs.getString("first_name");
				employee.last_name = rs.getString("last_name");
				employee.sex = res_sex;
				employee.join_date = rs.getString("join_date");
				employee.section = rs.getString("section");
				employee.mail = rs.getString("mail");
				employees.add(employee);
				// record_count++;
			}
		} catch (SQLException e) {
			throw new DBException("resultを読めないエラー",
					DBException.DB_RESULT_NOTREAD);
		}
		// DBクローズ
		try {
			db.close();
		} catch (Exception e) {
			throw new DBException("データベースクローズエラー", DBException.DB_CLOSE_ERR);
		}
		return employees;
	}

	// 検索用SQL文を作成
	private String createsql(String req_employee_no, String req_sex,
			String req_join_date_from, String req_join_date_to,
			String req_section, String req_action_id) {
		int i = 0;

		// 入力値チェックオブジェクト
		InputCheck inputcheck = new InputCheck(req_employee_no, req_sex,
				req_join_date_from, req_join_date_to, req_section);

		// 検索値がない場合
		if (inputcheck.searchcondition() == true) {
			sql += addsqlorder(req_action_id);
			// 検索値がある場合
		} else {
			sql += "WHERE ";
			if (req_employee_no != null && req_employee_no.length() > 0) {
				sql += "employee_no like '%" + req_employee_no + "%' ";
				i++;
			}
			if (req_sex != null && req_sex.compareTo("null") != 0) {
				if (i > 0) {
					sql += "and sex ='" + req_sex + "' ";
				} else {
					sql += "sex ='" + req_sex + "' ";
					i++;
				}
			}
			if (req_join_date_from != null && req_join_date_from.length() > 0) {
				if (i > 0) {
					sql += "and join_date >= " + req_join_date_from + " ";
				} else {
					sql += "join_date >= " + req_join_date_from + " ";
					i++;
				}
			}
			if (req_join_date_to != null && req_join_date_to.length() > 0) {
				if (i > 0) {
					sql += "and join_date <= " + req_join_date_to + " ";
				} else {
					sql += "join_date <= " + req_join_date_to + " ";
					i++;
				}
			}
			if (req_section != null && req_section.compareTo("null") != 0) {
				if (i > 0) {
					sql += "and section ='" + req_section + "' ";
				} else {
					sql += "section ='" + req_section + "' ";
				}
			}
			sql += addsqlorder(req_section);
		}
		return sql;
	}

	// 昇順・降順のチェック
	private String addsqlorder(String req_action_id) {
		String ordersql = "ORDER BY employee_no ASC;";
		if (req_action_id == "1") {
		} else if (req_action_id == "2") {
			ordersql = "ORDER BY employee_no DESC;";
		} else if (req_action_id == "3") {
			ordersql = "ORDER BY join_date ASC;";
		} else {
			ordersql = "ORDER BY join_date DESC;";
		}
		return ordersql;
	}
}
